const express = require('express')
const mysql = require('mysql')
const db = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: 'root',
    database: 'k3project'
})
const router=express.Router();
router.use(express.json()) //请求数据以json格式为主
router.use(express.urlencoded({extended:true}));//请求头的数据类型为 x/www-form-urlencoded
// 渲染
router.get('/dj01get',(req,res)=>{
    const page=req.query.page;
    const count=req.query.count;
    if(req.query.depValue=='全部'){
      req.query.depValue='%%'
    }
    const sql=`SELECT * FROM admin JOIN admin_dep ON admin.admin_dep=admin_dep.id where admin_name like 
    '%${req.query.nameValue}%' and admin_user like '%${req.query.userValue}%' and dep like '%${req.query.depValue}%' 
    LIMIT ${(page-1)*count},${count};`;
    const sql2=`SELECT COUNT(*) AS admin_id  FROM admin JOIN admin_dep ON admin.admin_dep=admin_dep.id  where admin_name like 
    '%${req.query.nameValue}%' and admin_user like '%${req.query.userValue}%' and dep like '%${req.query.depValue}%';` ;
    db.query(sql,(err,data)=>{
      if(err){
        // console.log(err);
        res.send({error:1,msg:err})
      }else{
        db.query(sql2,(err2,data2)=>{
          if(err2){
            // console.log(err2);
            res.send({error:1,msg:err2})
          }else{
          
            res.send({error:0,msg:"查询成功",data:data,total:data2[0].admin_id})
          }
        })
      }
    })
   
  })
// 删除
router.post('/dj01Del',(req,res)=>{
  console.log(req.body);
  const sql=`delete from admin where admin_id='${req.body.id}';`;
  db.query(sql,(err,data)=>{
    if(err){
      console.log(err);
      res.send({error:1,msg:err})
    }else{
      res.send({error:0,msg:'删除成功'})
    }
  })
});
// 查询修改的部门外键
router.post('/dj01Dep',(req,res)=>{
  const sql=`select admin_dep from admin where admin_id='${req.body.id}';`;
  db.query(sql,(err,data)=>{
    if(err){
      console.log(err);
      res.send({error:1,msg:err})
    }else{
      res.send({error:0,data:data})
    }
  })
})
// 修改
router.post('/dj01Revise',(req,res)=>{
  const sql=`update admin set admin_name='${req.body.userName}', admin_user='${req.body.userAdmin}',
  admin_nickname='${req.body.userNickname}',admin_dep='${req.body.userDep}',admin_tel='${req.body.userTel}'
  where admin_id='${req.body.id}';`;
  db.query(sql,(err,data)=>{
    if(err){
      console.log(err);
      res.send({error:1,msg:err})
    }else{
      res.send({error:0,msg:'修改成功'})
    }
  })

})
// 添加
router.post('/dj01Add',(req,res)=>{
  console.log(req.body);
  const sql=`INSERT INTO admin values(null,
  '${req.body.admin_name}','${req.body.admin_user}','${req.body.admin_psd}','${req.body.admin_nickname}', 
  '${req.body.admin_tel}','${req.body.admin_dep}');`;
  db.query(sql,(err,data)=>{
    if(err){
      console.log(err);
      res.send({error:1,msg:err})
    }else{
      res.send({error:0,msg:"添加成功",data:data})
    }
  })
})



//操作日志
//渲染
router.get('/dj02get',(req,res)=>{
  const page=req.query.page;
  const count=req.query.count;
  const sql=`SELECT * FROM admin JOIN admin_log ON admin.admin_id=admin_log.admin_id JOIN admin_dep ON admin.admin_dep=admin_dep.id
    where admin_name like '%${req.query.nameValue}%' and admin_user like '%${req.query.userValue}%' and login_id like 
   '%${req.query.idValue}%' order by login_id
  LIMIT ${(page-1)*count},${count} ;`;
  const sql2=`SELECT COUNT(*) AS  id  FROM admin_log JOIN admin ON admin.admin_id=admin_log.admin_id 
  JOIN admin_dep ON admin.admin_dep=admin_dep.id  where admin_name like 
  '%${req.query.nameValue}%' and admin_user like '%${req.query.userValue}%' and login_id like '%${req.query.idValue}%';` ;
  db.query(sql,(err,data)=>{
    if(err){
      console.log(err);
      res.send({error:1,msg:err})
    }else{
      db.query(sql2,(err2,data2)=>{
        if(err2){
          // console.log(err2);
          res.send({error:1,msg:err2})
        }else{
          res.send({error:0,msg:"查询成功",data:data,total:data2[0].id})
        }
      })
    }
  })
 
})
// 删除
router.post('/dj02Del',(req,res)=>{
  const sql=`delete from admin_log where login_id='${req.body.id}';`;
  db.query(sql,(err,data)=>{
    if(err){
      console.log(err);
      res.send({error:1,msg:err})
    }else{
      res.send({error:0,msg:'删除成功'})
    }
  })
});

router.post('/chart',(req,res)=>{
  const sql=`SELECT dep,COUNT(admin_id)as num FROM (SELECT * FROM admin JOIN admin_dep ON 
    admin.admin_dep=admin_dep.id)AS dep1 GROUP BY dep;`;
  db.query(sql,(err,data)=>{
    if(err){
      console.log(err);
      res.send({error:1,msg:err})
    }else{
      res.send({error:0,data:data})
    }
  })  
}
 
)


module.exports = db;
module.exports=router;
